﻿-- ==========================================================================================
-- Author:		Patrick <yuanjie@shanghai3h.com>
-- Create date: 2011-1-24
-- Description:	DataCenter database install script. 
--				This script can be executed just by sqlcmd utility. The section below is the 
--				definition of variable used in the script, which should be set by using the
--				argument -v in sqlcmd.
-- Variable:	db_DataCenter_Name		The name of DataCenter database.
--				db_DataCenter_Data_Path	The data and log file path of DataCenter database.
--				App_Path				The path of DataCenter.App.exe.
-- ==========================================================================================

-- *****************************************************
-- Create DataCenter database and tables
-- *****************************************************
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$(db_DataCenter_Name)')
	BEGIN
		CREATE DATABASE [$(db_DataCenter_Name)] ON PRIMARY 
			(NAME = N'$(db_DataCenter_Name)', FILENAME = N'$(db_DataCenter_Data_Path)\$(db_DataCenter_Name).mdf')
			LOG ON
			(NAME = N'$(db_DataCenter_Name)_log', FILENAME = N'$(db_DataCenter_Data_Path)\$(db_DataCenter_Name)_log.ldf')

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据库 $(db_DataCenter_Name) 失败'
		ELSE PRINT '[SUCCESS]	创建数据库 $(db_DataCenter_Name) 成功'
	END
GO

USE [$(db_DataCenter_Name)]
GO

IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'Component')
	BEGIN
		CREATE TABLE [$(db_DataCenter_Name)].[dbo].[Component](
			[ComponentId] [int] IDENTITY(1,1) NOT NULL,
			[ComponentName] [nvarchar](255) NOT NULL,
			[ComponentType] [int] NOT NULL,
			[ComponentConfig] [xml] NOT NULL,
			[AssemblyName] [nvarchar](1000) NOT NULL,
			[TypeName] [nvarchar](255) NOT NULL,
		CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED 
		(
			[ComponentId] ASC
		)) ON [PRIMARY]

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 Component 失败'
		ELSE PRINT '[SUCCESS]	创建数据表 Component 成功'
	END
GO

IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'DataCenterSetting')
	BEGIN
		CREATE TABLE [dbo].[DataCenterSetting](
			[MaxMemoryLimit] [int] NULL,
			[AppFilePath] [nvarchar](255) NULL,
			[LogFilePath] [nvarchar](255) NULL
		) ON [PRIMARY]	

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 DataCenterSetting 失败'
		ELSE 
			BEGIN
				PRINT '[SUCCESS]	创建数据表 DataCenterSetting 成功'

				-- Insert default value
				INSERT INTO [DataCenterSetting](MaxMemoryLimit, AppFilePath) VALUES('10485760', '$(App_Path)')
			END
	END
GO

IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'Object')
	BEGIN
		CREATE TABLE [dbo].[Object](
			[ObjectId] [int] NOT NULL,
			[ObjectType] [int] NOT NULL,
			[ObjectName] [nvarchar](255) NOT NULL,
			[ObjectKey] [nvarchar](255) NOT NULL,
			[VirtualObject] [int] NOT NULL,
			[BindingSourceId] [int] NULL,
			[BindingAdapterId] [int] NULL,
			[BindingProcessorId] [int] NULL,
			[BindingCalculatorId] [int] NULL,
			[DefaultValue] [float] NULL,
			[Unit] [float] NULL,
		CONSTRAINT [PK_Object] PRIMARY KEY CLUSTERED 
		(
			[ObjectId] ASC,
			[ObjectType] ASC
		)) ON [PRIMARY]

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 Object 失败'
		ELSE PRINT '[SUCCESS]	创建数据表 Object 成功'
	END
GO
	
--创建数据表ObjectType
IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'ObjectType')
	BEGIN
		CREATE TABLE [dbo].[ObjectType](
			[TypeId] [int] NOT NULL,
			[TypeName] [nvarchar](250) NULL,
		CONSTRAINT [PK_ObjectType] PRIMARY KEY CLUSTERED 
		(
			[TypeId] ASC
		)) ON [PRIMARY]

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 ObjectType 失败'
		ELSE PRINT '[SUCCESS]	创建数据表 ObjectType 成功'
	END
GO

--创建数据表RecentValueTable
IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'RecentValueTable')
	BEGIN
		CREATE TABLE [dbo].[RecentValueTable](
			[ObjectId] [int] NOT NULL,
			[ValueType] [int] NOT NULL,
			[ValueTime] [datetime] NOT NULL,
			[R__0] [real] NOT NULL,
		CONSTRAINT [PK_RecentValueTable] PRIMARY KEY CLUSTERED 
		(
			[ObjectId] ASC,
			[ValueType] ASC,
			[ValueTime] ASC
		)) ON [PRIMARY]

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 RecentValueTable 失败'
		ELSE PRINT '[SUCCESS]	创建数据表 RecentValueTable 成功'
	END
GO

--创建数据表TableIndexSetting
IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = N'TableIndexSetting')
	BEGIN
		CREATE TABLE [dbo].[TableIndexSetting](
			[TableName] [nvarchar](255) NOT NULL,
			[SplitMonthStep] [int] NOT NULL,
			[StartTableIndex] [int] NOT NULL,
			[EndTableIndex] [int] NOT NULL
		) ON [PRIMARY]

		IF @@ERROR <> 0 PRINT '[ERROR]	创建数据表 TableIndexSetting 失败'
		ELSE
			BEGIN 
				PRINT '[SUCCESS]	创建数据表 TableIndexSetting 成功'

				-- Insert default value
				INSERT INTO [TableIndexSetting](TableName, SplitMonthStep, StartTableIndex, EndTableIndex) 
				VALUES('DataTable', 1, 0, 0);
			END
	END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = N'DbDeploy')
	BEGIN
		DROP PROCEDURE [dbo].[DbDeploy]

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 DbDeploy 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 DbDeploy 成功'
	END
GO

CREATE PROCEDURE [dbo].[DbDeploy]
AS
BEGIN
	EXEC sp_configure 'clr enable', 1;
	RECONFIGURE;

	EXEC sp_changedbowner 'sa';
	ALTER DATABASE [$(db_DataCenter_Name)] SET TRUSTWORTHY ON;
END
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 DbDeploy 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 DbDeploy 成功'
GO